﻿using System.Data.SqlClient;
using BoutiquePOS.DAL.DAO;
using System;
using System.Collections.Generic;
using System.Data;

namespace BoutiquePOS.DAL.Gateway
{
    public class CompanyGateway:DbGateway
    {
        public string SaveCompany(Company aCompany)
        {
            try
            {
                ConnectionObj.Open();
                string query = String.Format("INSERT INTO PosCompany VALUES( @name)");
                CommandObj.Parameters.Clear();
                CommandObj.CommandText = query;
                CommandObj.Parameters.Add("@name", SqlDbType.VarChar);
                CommandObj.Parameters["@name"].Value = aCompany.CompanyName;
                CommandObj.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception("Company couldn't saved", ex);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
            return "Save successfully";
        }

        public List<Company> GetAllCompany()
        {
            List<Company> companies = new List<Company>();
            try
            {
                ConnectionObj.Open();
                string query = String.Format("SELECT * FROM PosCompany");

                CommandObj.CommandText = query;
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    Company aCompany = new Company();
                    aCompany.CompanyId = Convert.ToInt32(reader["CompanyId"]);
                    aCompany.CompanyName = reader["CompanyName"].ToString();
                    companies.Add(aCompany);
                }
            }
            catch (Exception exception)
            {
                throw new Exception("Error occurred during Company loading from your system.", exception);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
            return companies;
        }

        public Company FindCompany(int companyId)
        {
            try
            {
                string search = String.Format("Select * FROM PosCompany WHERE CompanyId=@CompanyId");
                ConnectionObj.Open();
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@CompanyId", SqlDbType.Int);
                CommandObj.Parameters["@CompanyId"].Value = companyId;
                CommandObj.CommandText = search;
                SqlDataReader sqlDataReader = CommandObj.ExecuteReader();
                Company Company = new Company();
                if (sqlDataReader != null)
                {
                    while (sqlDataReader.Read())
                    {
                        Company.CompanyId = Convert.ToInt32(sqlDataReader["CompanyId"]);
                        Company.CompanyName = sqlDataReader["CompanyName"].ToString();
                        return Company;
                    }
                }
                return null;
            }

            catch (Exception exception)
            {
                throw new Exception("Could not Found Company", exception);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
        }
        public string DeleteCompanyById(int companyId)
        {
            int i = 0;
            try
            {
                ConnectionObj.Open();
                string delete = String.Format("DELETE FROM PosCompany WHERE companyId='{0}'", companyId);
                CommandObj.CommandText = delete;
                i = CommandObj.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw new Exception("Database Error");
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
            if (i != 0)
            {
                return "Delete Successfully";
            }
            else
            {
                return "Company Id does not found";
            }
        }

        public List<Company> GetAllCompanyByCodeOrName(int i, string companyId)
        {
            List<Company> companies1 = new List<Company>();
            string select = "";

            try
            {
                ConnectionObj.Open();
                if (i == 0)
                {
                   
                    select = String.Format("SELECT * FROM PosCompany WHERE companyId ='{0}'",Convert.ToInt32(companyId));
                }
                if (i == 1)
                {
                    companyId = companyId + "%";
                    select = String.Format("SELECT * FROM PosCompany WHERE companyName LIKE '" + companyId + "'");
                }

                CommandObj.CommandText = select;
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    Company company = new Company();
                    company.CompanyName = reader[1].ToString();
                    company.CompanyId = Convert.ToInt32(reader[0]);
                    companies1.Add(company);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
            return companies1;
        }

    }
}